********************************************************************************
*                                                                              *
*                     Final Dataset PS Descriptive Statistics                  *
*                                                                              *
********************************************************************************

cd "H:\"


////////////////////////////////////////////////////////////////////////////////
///////////////////////////Create descriptive tables////////////////////////////
////////////////////////////////////////////////////////////////////////////////


clear all

* Create Table 1

use "Robots and Workers\Data\Workers_PS_LFS_panel_full.dta", clear
keep if hwweight == 1
sort rinpersoon year
g income_adj = income_gross
g fyear = year if nonempl == 0
g unemplyears = 0
replace unemplyears = nonempl+unemplyears[_n-1] if nonempl==1 & rinpersoon[_n-1]==rinpersoon
foreach var in beid sbi2 sbi3 sbi4 mun compete robots robotsfirm manufacturing logvalueaddedpworker09 logworkers09 income_gross fyear {
	if "`var'" != "income_gross" {
		replace `var' = `var'[_n-1] if nonempl==1 & rinpersoon[_n-1]==rinpersoon
	}
	if "`var'" == "income_gross" {
		replace income_adj = 0.7*income_adj[_n-unemplyears] if nonempl==1 & rinpersoon[_n-1]==rinpersoon
	}
}
capture egen beidXyear = group(beid year)
keep if include==1 & include_post2009==1
g logincome_adj = ln(income_adj)
g empl = 1-nonempl
replace empl = . if (jobtype_selfempl==1 | jobtype_retired==1 | jobtype_student==1 | jobtype_other==1 | jobtype_employee ==.)
capture g sbi2 = substr(sbi,1,2)
capture destring sbi2, force replace
merge m:1 beid fyear using "Robots and Workers\Data\BEID_shiftshare.dta", nogen keep(1 3)
egen industry_ifrXyear = group(industry_ifr year)
keep if manufacturing==1
merge m:1 isco08 using "Robots and Workers\Base files\Replaceable.dta", keep(1 3 4 5) keepusing(bluecollar replaceable replaceable_orig meffect routine) update replace nogen

preserve
	keep hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	*keep if (hwage !=. | unempl !=.) & compete !=. & meffect !=. & replaceable !=. & robots !=.
	drop if hwage == . & (hoursworked == 0 | hoursworked ==.)  & empl == .
	order hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_workers", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	
preserve
	keep if robotsfirm==0
	keep hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	*keep if (hwage !=. | unempl !=.) & compete !=. & meffect !=. & replaceable !=. & robots !=.
	drop if hwage == . & (hoursworked == 0 | hoursworked ==.)  & empl == .
	order hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_workers_norobots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	
preserve
	keep if robotsfirm==1
	keep hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	*keep if (hwage !=. | unempl !=.) & compete !=. & meffect !=. & replaceable !=. & robots !=.
	drop if hwage == . & (hoursworked == 0 | hoursworked ==.)  & empl == .
	order hwage hoursworked empl earnings robots robotsfirm compete meffect replaceable lowskilled male age migrant migrant_2ndgen hh_single hh_couple hh_other
	global N = _N
	outreg2 using "Robots and Workers\Results\Descriptives_workers_robots", replace sum(detail) eqkeep(mean sd p5 p50 p95 N) label word tex addnote(The number of observations is $N.)
restore	


////////////////////////////////////////////////////////////////////////////////
////////////CALCULATE WORKER TRENDS AND LINK TO PERSONAL CHARACTERISTICS////////
////////////////////////////////////////////////////////////////////////////////

* Create Figure 3

////////////////////////// Match LEED data to LFS //////////////////////////////

forvalues year = 2000(1)2016 {
	use "Robots and Workers\Data\Baansommen_`year'.dta", clear
	g lfsyear = `year'
	merge m:1 rinpersoon lfsyear using "Robots and Workers\Data\LFSX_before.dta", nogen keep(1 2 3 4 5) update keepusing(isco08 educ_soi5 educ_soi3 ebbcdlaber92ni)
	merge m:1 rinpersoon lfsyear using "Robots and Workers\Data\LFSX_extnew.dta", nogen keep(1 2 3 4 5) update keepusing(isco08 educ_soi5 educ_soi3 isco08_extnew)
	drop if isco08 == . & isco08_extnew == .
	drop if beid ==.
	g baansommen = 1
	save "Robots and Workers\Data\temp_Baansommen_Workertrends_`year'.dta", replace
}	

forvalues year = 2010(1)2020 {
	use "Robots and Workers\Data\SPolis_`year'.dta", clear
	g lfsyear = `year'
	merge m:1 rinpersoon lfsyear using "Robots and Workers\Data\LFSX_before.dta", nogen keep(1 2 3 4 5) update keepusing(isco08 educ_soi5 educ_soi3 ebbcdlaber92ni)
	merge m:1 rinpersoon lfsyear using "Robots and Workers\Data\LFSX_extnew.dta", nogen keep(1 2 3 4 5) update keepusing(isco08 educ_soi5 educ_soi3 isco08_extnew)
	replace income_gross = income_beftax if income_beftax!=.
	drop if isco08 == . & isco08_extnew == .
	drop if beid ==.
	save "Robots and Workers\Data\temp_SPOLIS_Workertrends_`year'.dta", replace
}	

clear all
forvalues year = 2000(1)2020 {
	capture append using "Robots and Workers\Data\temp_Baansommen_Workertrends_`year'.dta"
	capture erase "Robots and Workers\Data\temp_Baansommen_Workertrends_`year'.dta"
	capture append using "Robots and Workers\Data\temp_SPOLIS_Workertrends_`year'.dta"
	capture erase "Robots and Workers\Data\temp_SPOLIS_Workertrends_`year'.dta"
}

replace baansommen = 0 if baansommen == .

save "Robots and Workers\Data\temp_Workertrends.dta", replace	

use "Robots and Workers\Data\temp_Workertrends.dta", clear

foreach var in contracthours baanid flexcontract companycar lfsyear ikvid Δjobchange Δfirmchange hwage {
	 capture drop `var'
}
	
////////////////////////////// Obtain RTI data /////////////////////////////////

rename (isco08 year) (ISCO08Code lfsyear)
forvalues year = 1996(1)2021 {
		merge m:1 ISCO08Code lfsyear using "Robots and Workers\Base Files\RTI\onet-ISCO08RTI_LFSX_`year'.dta", nogen keep(1 3 4 5) update keepusing(RTI* nr_cog_anal nr_cog_pers r_cog r_man nr_man_phys nr_man_pers nr_man_persInt)
}
rename (RTI RTIw ISCO08Code lfsyear) (rti rtiw isco08 year)
drop rtiw


merge m:1 isco08 using "Robots and Workers\Base Files\Replaceable.dta", keep(1 2 3) nogen keepusing(replaceable_orig replaceable bluecollar)
	
g lowskilled = educ_soi3 == 1 if educ_soi3!=.
g lowmedskilled = educ_soi3 == 1 | educ_soi3 == 2 if educ_soi3!=.

foreach var in nr_cog_anal nr_cog_pers r_cog r_man nr_man_phys nr_man_pers nr_man_persInt {
	capture drop `var'
}

compress
save "Robots and Workers\Data\temp_Workertrends.dta", replace	

/////////////////////////// Calculate worker trends ////////////////////////////

use "Robots and Workers\Data\temp_Workertrends.dta", clear
g hwage = (income_gross/hoursworked)
g loghwage = ln(income_gross/hoursworked)
g loghoursworked = ln(hoursworked)	

g routine = rti>1 if rti !=.
g meffect = routine == 1 & bluecollar==1 if routine!=. & bluecollar !=.
tabstat routine bluecollar replaceable meffect lowskilled, by(year)	

preserve
	g hwageXother = hwage if (rti<1 & bluecollar==0 & rti !=. & replaceable==0 & lowskilled==0)
	g hwageXrti = hwage if (rti>1 & rti !=.)
	g hwageXbluecollar = hwage if (bluecollar==1)
	g hwageXmeffect = hwage if (bluecollar==1 & rti>1 & rti !=.)
	g hwageXreplaceable = hwage if (replaceable==1 & replaceable!=.)
	g hwageXlowskilled = hwage if (lowskilled==1 & lowskilled!=.)
	collapse (mean) hwage*, by(year baansommen)
	tabstat hwage*, by(year), if baansommen==1
	tabstat hwage*, by(year), if baansommen==0
restore	


preserve
	g hoursworkedXother = (1/1000)*hoursworked*(rti<1 & bluecollar==0 & replaceable==0 & lowskilled==0) if rti !=.
	g hoursworkedXrti = (1/1000)*hoursworked*(rti>1) if rti !=.
	g hoursworkedXbluecollar = (1/1000)*hoursworked*(bluecollar==1) if rti !=.
	g hoursworkedXmeffect = (1/1000)*hoursworked*(bluecollar==1 & rti>1) if rti !=. & bluecollar!=.
	g hoursworkedXreplaceable = (1/1000)*hoursworked*(replaceable==1) if replaceable!=.
	g hoursworkedXlowskilled = (1/1000)*hoursworked*(lowskilled==1) if lowskilled!=.
	collapse (sum) hoursworked*, by(year baansommen)
	
	replace hoursworked = hoursworked/1000
	tabstat hoursworked*, by(year), if baansommen==1
	tabstat hoursworked*, by(year), if baansommen==0
restore
	
tabstat routine bluecollar  meffect replaceable lowskilled, by(year)


////////////////////////////////////////////////////////////////////////////////
///////////////////////////////Create histograms////////////////////////////////
////////////////////////////////////////////////////////////////////////////////

* Figures not in paper

use "Robots and Workers\Data\Workers_PS_LFS_panel_full.dta", clear
keep if hwweight == 1
sort rinpersoon year
keep if include==1 & include_post2009==1

putexcel set "Robots and Workers\Results\Figures.xlsx", modify sheet(Histograms_workers)
serset drop _all

preserve 
	drop if loghoursworked < 6 | loghoursworked > 8
	histogram loghoursworked, start(6) width(0.02)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms_workers") sheetmodify cell(A25) firstrow(variables)
	putexcel A25 = "Log of hours worked"
	putexcel B25 = ""
	putexcel C25 = ""
restore

preserve 
	drop if logincome_gross < 7 | logincome_gross > 12.5
	histogram logincome_gross, start(7) width(0.05)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms_workers") sheetmodify cell(E25) firstrow(variables)
	putexcel E25 = "Log of annual earnings"
	putexcel F25 = ""
	putexcel G25 = ""
restore

preserve 
	drop if loghwage < 2 | loghwage > 5
	histogram loghwage, start(2) width(0.025)
	graph save "Graph" "Robots and Workers\Results\Graph.gph", replace
	clear
	graph use "Robots and Workers\Results\Graph.gph"
	serset dir
	serset use
	export excel using "Robots and Workers\Results\Figures.xlsx", sheet("Histograms_workers") sheetmodify cell(I25) firstrow(variables)
	putexcel I25 = "Log of hourly wage"
	putexcel J25 = ""
	putexcel K25 = ""
restore



